<?php

namespace app\common\func;

use app\admin\model\User;
use app\common\model\Allocation;
use app\common\model\AllocationDetails;
use app\common\model\BalanceTotal;
use app\common\model\BalanceType;
use app\common\model\BankCard;
use app\common\model\Cash;
use app\common\model\Company;
use app\common\model\CompanyLevel;
use app\common\model\CompanyStocks;
use app\common\model\CompanyStocksDetails;
use app\common\model\ConsumeChannel;
use app\common\model\Equip;
use app\common\model\EquipFigure;
use app\common\model\Flow;
use app\common\model\FlowSft;
use app\common\model\Money;
use app\common\model\MoneyCash;
use app\common\model\MoneyFlow;
use app\common\model\MoneyLog;
use app\common\model\MoneyLogs;
use app\common\model\MoneyProfit;
use app\common\model\MoneyStandard;
use app\common\model\Policy;
use app\common\model\PolicyCash;
use app\common\model\PolicyProfit;
use app\common\model\PolicyRelation;
use app\common\model\PolicySimcard;
use app\common\model\PolicyStandard;
use app\common\model\Product;
use app\common\model\ProductTemplate;
use app\common\model\Profit;
use app\common\model\ProfitDetails;
use app\common\model\Rate;
use app\common\model\SmsLogs;
use app\common\model\StandardMoney;
use app\common\model\StandardRecord;
use app\common\model\Stardard;
use app\common\model\transaction\Xyf;
use app\common\model\TransactionType;
use app\common\model\User as ModelUser;
use think\Db;

class Lib
{

    /**
     * 根据SN查询盛付通流量卡信息
     * @param $sn
     * @return array|bool|false|\PDOStatement|string|\think\Model|null
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getFlowSft($sn)
    {
        return FlowSft::where(["sn" => $sn])->find();
    }

    public static function getFlowSftByNum($orderNumber)
    {
        return FlowSft::where(["order_num" => $orderNumber])->find();
    }

    public static function getAllocationCount($mainType, $mainId, $productId)
    {
        return Db::name("allocation")->alias("a")
            ->join("policy b", "a.policy_id = b.id", "LEFT")
            ->where(["a.to_type" => $mainType, "a.to" => $mainId, "b.product_id" => $productId])
            ->where(["a.confirm_type" => 0])
            ->count();
    }

    public static function getAllocationCount5($mainType, $mainId, $productId)
    {
        return Db::name("allocation")
            ->where(["to_type" => $mainType, "to" => $mainId, "product_id" => $productId])
            ->where(["confirm_type" => 0])
            ->count();
    }

    public static function getProductByPolicy($policyId)
    {
        return Db::name("product")->alias("p")
            ->join("policy policy", "policy.product_id = p.id", "LEFT")
            ->where(["policy.id" => $policyId])
            ->field("p.*")
            ->find();
    }

    public static function getPolicyProfitList($policyId)
    {
        return PolicyProfit::where(["policy_id" => $policyId])->select();
    }

    public static function getPolicyProfitByParams($policyId, $cardType, $tran, $rate, $channel)
    {
        return PolicyProfit::where(["policy_id" => $policyId, "card_type" => $cardType, "transaction_type_id" => $tran, "rate_id" => $rate, "channel_id" => $channel])->find();
    }

    public static function getUserFreeEquip($mainType, $mainId, $productId)
    {
        return Equip::where(["main_type" => $mainType, "main_id" => $mainId, "product_id" => $productId, "active_time" => 0, "binding_time" => 0])->select();
    }

    /**
     * 查询用户设备对应的产品列表
     * @param $mainType
     * @param $mainId
     * @return bool|false|\PDOStatement|string|\think\Collection
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getEquipProduct($mainType, $mainId)
    {
        return Db::name("equip")->alias("e")
            ->join("product p", "e.product_id = p.id", "LEFT")
            ->where(["e.main_type" => $mainType, "e.main_id" => $mainId])
            ->group("e.product_id")
            ->field("p.*")
            ->select();
    }

    public static function getPolicyBySn($sn)
    {
        return Db::name("policy")->alias("p")
            ->join("equip e", "p.id = e.policy_id", "LEFT")
            ->where(["e.sn" => $sn])
            ->field("p.*")
            ->find();
    }

    public static function getXyfFlowBySn($sn)
    {
        return Xyf::where(["sn" => $sn, "is_flow" => "1"])->select();
    }

    public static function getUnStandardEquip()
    {
        return StandardRecord::where(["status" => 0])->where(" total_money >= require_money")->select();
    }

    public static function getEquipStandardRecord($sn, $bindingTime)
    {
        $list = StandardRecord::where(["sn" => $sn])->select();

        if (count($list) > 0) {
            foreach ($list as $key => $val) {
                $val->start = self::roundTime($val->start_day, $bindingTime) + 1;
                $val->end = self::roundTime($val->end_day, $bindingTime);
            }
        }

        return $list;
    }

    public static function roundTime($endTime, $startTime)
    {
        return ($endTime - $startTime) / (60 * 60 * 24);
    }

    public static function getFigureBySn($sn)
    {
        return EquipFigure::where(["sn" => $sn])->find();
    }

    public static function getUnFlowEquip()
    {
        //        $now = time();
        //        $yesterday = date("Y-m-d",strtotime("-1 day",$now));
        return EquipFigure::where(["flow" => "0", "flow_status" => "1"])->select();
    }

    public static function getUncashEquip()
    {
        //        $now = time();
        //        $yesterday = date("Y-m-d",strtotime("-1 day",$now));
        return EquipFigure::where(["cash" => "0"])->select();
    }

    public static function getEquipManage($mainType, $mainId)
    {
        return Db::name("equip")->alias("e")
            ->join("product p", "e.product_id = p.id", "LEFT")
            ->where(["e.main_type" => $mainType, "e.main_id" => $mainId])
            ->group("e.product_id")
            ->field("p.id as product_id,p.name,COUNT(*) AS total,COUNT(active_time > 0 OR NULL) as active,COUNT(binding_time > 0 OR NULL) as bind")
            ->select();
    }

    public static function getMoneyDetails($mainType, $mainId, $balanceType, $pageSize)
    {
        switch ($balanceType) {
            case 1:
                $model = new MoneyProfit();
                break;
            case 2:
                $model = new MoneyCash();
                break;
            case 3:
                $model = new MoneyFlow();
                break;
            case 4:
                $model = new MoneyStandard();
                break;
        }

        return $model->where(["main_type" => $mainType, "main_id" => $mainId])->order("create_time desc")->paginate($pageSize);
    }

    /**
     * 账户明细 产品卡片
     * @param $mainType
     * @param $mainId
     * @param $balanceType
     * @param $startTime
     * @param $endTime
     * @return bool|false|\PDOStatement|string|\think\Collection
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getMoneyCard($mainType, $mainId, $balanceType, $startTime, $endTime)
    {
        switch ($balanceType) {
            case 1:
                $model = new MoneyProfit();
                break;
            case 2:
                $model = new MoneyCash();
                break;
            case 3:
                $model = new MoneyFlow();
                break;
            case 4:
                $model = new MoneyStandard();
                break;
        }
        $query = $model->alias("m")
            ->join("product p", "m.product_id = p.id", "LEFT")
            ->where(["m.main_type" => $mainType, "m.main_id" => $mainId]);

        if ($startTime && $endTime) {
            $query->where(["m.create_time" => [">=", $startTime]])->where(["m.create_time" => ["<", $startTime]]);
        }

        return $query->group("m.product_id")
            ->field("p.name,SUM(changes) as sum")
            ->select();
    }

    public static function getPolicyRelationByPolicy($policyId)
    {
        return PolicyRelation::where(["policy_id" => $policyId])->count();
    }

    public static function getPolicyBySetting($type, $id)
    {
        switch ($type) {
            case "profit":
                $where = ["profit_id" => $id];
                break;
            case "cash":
                $where = ["cash_id" => $id];
                break;
            case "flow":
                $where = ["flow_id" => $id];
                break;
            case "standard":
                $where = ["stardard_id" => $id];
                break;
            default:
                $where = [];
        }

        return Policy::where($where)->select();
    }

    public static function getPolicyRelation($mainType, $mainId, $productId, $policyId)
    {
        return PolicyRelation::where(["main_type" => $mainType, "main_id" => $mainId, "product_id" => $productId, "policy_id" => $policyId])->find();
    }

    public static function getPolicyByProduct($mainType, $mainId, $productId)
    {
        return Db::name("policy")->alias("policy")
            ->where(["policy.product_id" => $productId, "policy.main_type" => $mainType, "policy.main_id" => $mainId, "policy.status" => 1])
            ->field("policy.*")
            ->select();
    }

    public static function getStockConfirm($companyId, $sn)
    {
        return Db::name("company_stocks_confirm")->alias("c")
            ->join("company_stocks_details d", "c.id = d.confirm_id", "LEFT")
            ->where(["d.sn" => $sn, "c.company_id" => $companyId, "c.confirm_type" => 1])
            ->find();
    }

    public static function getPolicyRelationList($mainType, $mainId)
    {
        return Db::name("policy")->alias("p")
            ->join("policy_relation r", "p.id = r.policy_id", "LEFT")
            ->where(["r.main_type" => $mainType, "r.main_id" => $mainId])
            ->field("p.*")
            ->select();
    }

    /**
     * 查询达标奖励记录
     * @param $mainType
     * @param $mainId
     * @param $sn
     * @param $dayLimit
     * @param $money
     * @param $moneyType
     * @return array|bool|false|\PDOStatement|string|\think\Model|null
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getStandardMoney($mainType, $mainId, $sn, $dayLimit, $money, $moneyType)
    {
        return StandardMoney::where(["main_type" => $mainType, "main_id" => $mainId, "day_limit" => $dayLimit, "money" => $money, "sn" => $sn, "money_type" => $moneyType])->find();
    }

    /**
     * 根据产品、卡类型、交易类型、费率查询分润
     * @param $profitId
     * @param $cardType
     * @param $tranId
     * @param $rateId
     * @param $channelId
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getProfitRateByParams($profitId, $cardType, $tranId, $rateId, $channelId)
    {
        return Db::name("profit_details")
            ->where(["profit_id" => $profitId, "card_type" => $cardType, "transaction_type_id" => $tranId, "rate_id" => $rateId, "channel_id" => $channelId])
            ->find();
    }

    public static function getUserAllocationList($mainType, $mainId, $type = [1])
    {
        return Allocation::where(["from_type" => $mainType, "from" => $mainId, "type" => ["in", $type], "confirm_type" => 0])->select();
    }

    public static function getUnbindEquip($mainType, $mainId, $productId)
    {
        $allocationList = self::getUserAllocationList($mainType, $mainId);
        $list = [];
        foreach ($allocationList as $key => $val) {
            if ($val->data_type != 1) {
                $sn = explode(",", $val->sn);
                $list = array_merge($list, $sn);
            }
        }

        return Equip::where(["main_type" => $mainType, "main_id" => $mainId, "product_id" => $productId])->where(["binding_time" => 0])
            ->whereNotIn("sn", $list)->select();
    }

    public static function getTranDetails($mainType, $mainId)
    {
        return Db::name("transaction_data_xyf")->alias("xyf")
            ->join("equip e", "xyf.sn = e.sn", "LEFT")
            ->where(["e.main_type" => $mainType, "e.main_id" => $mainId])
            ->field("xyf.*")
            ->select();
    }

    public static function getTranDataList($mainType, $mainId, $pageSize, $startTime = 0, $endTime = 0)
    {
        $query = Db::name("transaction_data_xyf")->alias("xyf")
            ->join("equip e", "xyf.sn = e.sn", "LEFT")
            ->where(["e.main_type" => $mainType, "e.main_id" => $mainId]);

        if ($startTime > 0 && $endTime > 0 && $startTime < $endTime) {
            $query->whereBetween("xyf.create_time", [$startTime, $endTime]);
        }

        return $query->paginate($pageSize);
    }

    public static function getTranData($mainType, $mainId, $startTime = 0, $endTime = 0)
    {
        $query = Db::name("transaction_data_xyf")->alias("xyf")
            ->join("equip e", "xyf.sn = e.sn", "LEFT")
            ->where(["e.main_type" => $mainType, "e.main_id" => $mainId]);

        if ($startTime > 0 && $endTime > 0 && $startTime < $endTime) {
            $query->whereBetween("xyf.create_time", [$startTime, $endTime]);
        }

        return $query->field("COUNT(*) as total,IFNULL(SUM(`xyf`.`transaction_amount`),0) AS sum")
            ->find();
    }

    public static function getEquipInfo($id)
    {
        return Db::name("equip")->alias("e")
            ->join("product p", "e.product_id = p.id", "LEFT")
            ->join("policy policy", "e.policy_id = policy.id", "LEFT")
            ->where(["e.id" => $id])
            ->field("e.main_type,e.main_id,p.name,p.price,p.image,policy.title")
            ->find();
    }

    public static function getMoneyList($mainType, $mainId, $type, $pageSize, $startTime, $endTime)
    {
        $query = Db::name("money_log")->alias("log")
            ->join("balance_type b", "log.balance_type_id = b.id", "LEFT")
            ->where(["type" => 1])
            ->where(["log.main_type" => $mainType, "log.main_id" => $mainId, "log.balance_type_id" => $type]);

        if ($startTime > 0 && $endTime > 0) {
            $query->whereBetween("log.create_time", [$startTime, $endTime]);
        }

        return $query->order("create_time desc")
            ->field("log.*,b.name")
            ->paginate($pageSize);
    }

    public static function getUserMoney($mainType, $mainId)
    {
        return Db::name("money")->alias("money")
            ->join("balance_type b", "money.balance_type_id = b.id", "LEFT")
            ->where(["main_type" => $mainType, "main_id" => $mainId, "b.status" => 1])
            ->order("money.balance_type_id")
            ->field("money.*,b.name")
            ->select();
    }

    public static function getProductBySn($sn)
    {
        return Db::name("product")->alias("p")
            ->join("equip e", "e.product_id = p.id", "LEFT")
            ->where(["e.sn" => $sn])
            ->find();
    }

    public static function getActiveEquipList($mainType, $mainId, $pageSize)
    {
        return Equip::where(["main_type" => $mainType, "main_id" => $mainId, "active_time" => [">", 0]])->order("active_time desc")->paginate($pageSize);
    }

    public static function getUserActiveEquip($mainType, $mainId, $startTime = 0, $endTime = 0)
    {
        $childs = Tools::getUserChild($mainId, 4);
        array_push($childs, $mainId);
        $query = Equip::where(["main_type" => $mainType, "main_id" => ["in", $childs]])->where("active_time", ">", 0);

        if ($startTime > 0 && $endTime > 0 && $startTime < $endTime) {
            $query->whereBetween("active_time", [$startTime, $endTime]);
        }

        return $query->count();
    }

    public static function getChildUserList($mainType, $mainId, $pageSize)
    {
        return \app\common\model\User::where(["ptype" => $mainType, "pid" => $mainId])->field("id,group_id,ptype,pid,username,nickname,mobile,avatar,FROM_UNIXTIME(createtime, '%Y-%m-%d %H:%m:%s') createtime")->order("createtime desc")->paginate($pageSize);
    }

    public static function getChildUsers($mainType, $mainId, $startTime = 0, $endTime = 0)
    {
        $query = \app\common\model\User::where(["ptype" => $mainType, "pid" => $mainId]);

        if ($startTime > 0 && $endTime > 0 && $startTime < $endTime) {
            $query->whereBetween("createtime", [$startTime, $endTime]);
        }

        return $query->select();
    }

    public static function getTranList($mainType, $mainId, $productId, $startTime = 0, $endTime = 0, $pageSize)
    {
        $query = Db::name("transaction_data_xyf")->alias("xyf")
            ->join("equip e", "xyf.sn = e.sn", "LEFT")
            ->where(["e.main_type" => $mainType, "e.main_id" => $mainId])
            ->where(["e.product_id" => $productId]);

        if ($startTime > 0 && $endTime > 0 && $startTime < $endTime) {
            $query->where(["xyf.create_time" => [">=", $startTime]])->where(["xyf.create_time" => ["<", $endTime]]);
        }

        return $query->field("e.sn,xyf.transaction_amount,xyf.transaction_commission,xyf.order_num,xyf.trading_time")
            ->paginate($pageSize);
    }

    public static function getTranCountList($mainType, $mainId, $dataType = "month", $pageSize = 15)
    {
        if ($dataType == "month") {
            $format = "FROM_UNIXTIME( xyf.create_time, '%Y-%m' )";
        } elseif ($dataType == "day") {
            $format = "FROM_UNIXTIME( xyf.create_time, '%Y-%m-%d' )";
        }

        $query = Db::name("transaction_data_xyf")->alias("xyf")
            ->join("equip e", "xyf.sn = e.sn", "LEFT")
            ->join("product p", "p.id = e.product_id", "LEFT")
            ->where(["e.main_type" => $mainType, "e.main_id" => $mainId]);

        return $query->field("COUNT(*) as total,IFNULL(SUM(`xyf`.`transaction_amount`),0) AS sum,{$format} as date,e.product_id,p.name")
            ->group("e.product_id,{$format}")->order("date desc")
            ->paginate($pageSize);
    }

    public static function getEquipCountList($mainType, $mainId, $productId, $startTime, $endTime, $page, $pageSize)
    {
        return Db::name("equip")->where(["main_type" => $mainType, "main_id" => $mainId])
            ->where(["product_id" => $productId])->where(["active_time" => [">=", $startTime]])->where(["active_time" => ["<", $endTime]])
            ->order("active_time desc")->page($page, $pageSize)->select();
    }

    public static function getEquipCount($mainType, $mainId, $startTime, $endTime)
    {
        $query = Db::name("equip")->alias("e")
            ->join("product p", "e.product_id = p.id", "LEFT")
            ->where(["e.main_type" => $mainType, "e.main_id" => $mainId]);

        if ($startTime && $endTime) {
            $query->where(["e.active_time" => [">=", $startTime]])->where(["e.active_time" => ["<", $endTime]]);
        }

        return  $query->field("COUNT(*) as total,p.id as product_id,p.name as product_name")->group("p.id")->order("p.id desc")->select();
    }

    public static function getMonthIncome($mainType, $mainId, $type, $dataType)
    {
        if ($dataType == "month") {
            $format = "FROM_UNIXTIME( create_time, '%Y-%m' )";
        } elseif ($dataType == "day") {
            $format = "FROM_UNIXTIME( create_time, '%Y-%m-%d' )";
        }
        return Db::name("money_log")
            ->where(["main_type" => $mainType, "main_id" => $mainId, "type" => $type])
            ->group("date")
            ->field("COUNT(*) as total,{$format} AS date,SUM( changes ) as sum")
            ->order("date desc")
            ->select();
    }

    public static function getBankCard($userId)
    {
        return BankCard::where(["sale_id" => $userId])->find();
    }

    public static function getTemplateByType($productId, $type)
    {
        return ProductTemplate::where(["product_id" => $productId, "type" => $type])->find();
    }

    public static function getEquipByPolicy($policyId)
    {
        return Equip::where(["policy_id" => $policyId, "status" => 1])->count();
    }

    public static function getStandardByIds($ids)
    {
        return Stardard::where("id", "in", $ids)->order("day_limit asc")->select();
    }

    public static function getStandardById($id)
    {
        return Stardard::get($id);
    }

    public static function getFlowById($policyId)
    {
        return PolicySimcard::where(["policy_id" => $policyId])->find();
    }

    public static function getCashByPolicy($policyId)
    {
        return PolicyCash::where(["policy_id" => $policyId])->find();
    }

    public static function getProfitById($profitId)
    {
        return Profit::get($profitId);
    }

    public static function getUserStandard($mainType, $mainId)
    {
        return Stardard::where(["main_type" => $mainType, "main_id" => $mainId])->find();
    }

    public static function getUserCash($mainType, $mainId)
    {
        return Cash::where(["main_type" => $mainType, "main_id" => $mainId])->find();
    }

    /**
     * 查询返现记录
     * @param $mainType
     * @param $mainId
     * @param $sn
     * @param $balanceId
     * @return array|bool|false|\PDOStatement|string|\think\Model|null
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getMoneyLog($mainType, $mainId, $sn, $balanceId)
    {
        return MoneyLogs::where(["main_type" => $mainType, "main_id" => $mainId, "sn" => $sn, "balance_type_id" => $balanceId])->find();
    }

    public static function getEquipTotalMoney($sn)
    {
        return Xyf::where(["sn" => $sn])->sum("transaction_amount");
    }

    public static function getProductTemplateByProduct($productId)
    {
        return ProductTemplate::where(["product_id" => $productId])->find();
    }

    /**
     * @param $type
     * @param $id
     * @param $productId
     * @param $allocationType 1=接收方，2=设置方
     * @param $confirm || 接收状态:1=已接收,2=拒绝,0=接受中
     * @param $pageSize
     * @param $search
     * @return \think\Paginator
     * @throws \think\exception\DbException
     */
    public static function getAllocationList($type, $id, $productId, $allocationType, $confirm, $pageSize, $search)
    {
        if ($allocationType == 1) {
            $where = ["to_type" => $type, "to" => $id];
        } else {
            $where = ["from_type" => $type, "from" => $id];
        }
        $query = Db::name("allocation")->alias("a")
            ->join("user u", "a.to = u.id", "LEFT")
            ->join("company p", "a.to = p.id", "LEFT")
            ->where($where)->where(["confirm_type" => $confirm])
            ->where(["a.product_id" => $productId]);

        if ($search && $allocationType == 2) {
            $query->where("u.nickname", "like", "%" . $search . "%");
        }

        return $query->order("create_time desc")
            ->field("a.*,u.nickname,p.name")
            ->paginate($pageSize);
    }

    public static function getPolicyList($mainType, $mainId, $search = "", $pageSize = 15)
    {
        $query = Policy::where(["main_type" => $mainType, "main_id" => $mainId, "status" => 1]);

        if (!empty($search)) {
            $query->where("title", "like", "%" . $search . "%");
        }

        return $query->order("create_time desc")->paginate($pageSize);
    }

    public static function getStandardList($mainType, $mainId, $pageSize)
    {
        return Stardard::where(["main_type" => $mainType, "main_id" => $mainId, "status" => 1])->order("create_time desc")->paginate($pageSize);
    }

    public static function getFlowList($mainType, $mainId, $pageSize)
    {
        return Flow::where(["main_type" => $mainType, "main_id" => $mainId, "status" => 1])->order("create_time desc")->paginate($pageSize);
    }

    public static function getFlowObj($mainType, $mainId)
    {
        return Flow::where(["main_type" => $mainType, "main_id" => $mainId, "status" => 1])->order("create_time desc")->find();
    }

    public static function getCashList($mainType, $mainId, $pageSize)
    {
        return Cash::where(["main_type" => $mainType, "main_id" => $mainId, "status" => 1])->order("create_time desc")->paginate($pageSize);
    }

    public static function getCashObj($mainType, $mainId)
    {
        return Cash::where(["main_type" => $mainType, "main_id" => $mainId, "status" => 1])->order("create_time desc")->find();
    }

    public static function getProfitList($mainType, $mainId, $search = "", $pageSize = 15)
    {
        $query = Profit::where(["main_type" => $mainType, "main_id" => $mainId, "status" => 1]);

        if (!empty($search)) {
            $query->where("title", "like", "%" . $search . "%");
        }

        return $query->order("create_time desc")->paginate($pageSize);
    }

    public static function getProductByIds($ids)
    {
        return Product::where("id", "in", $ids)->select();
    }

    public static function getStandards($mainType, $mainId)
    {
        return Stardard::where(["main_type" => $mainType, "main_id" => $mainId])->order("day_limit asc")->select();
    }

    public static function getProfitByUser($mainType, $mainId)
    {
        return Db::name("profit")->alias("profit")
            ->join("policy p", "p.profit_id = profit.id", "LEFT")
            ->join("allocation a", "a.policy_id = p.id", "LEFT")
            ->where(["a.to_type" => $mainType, "a.to" => $mainId])
            ->group("profit.id")
            ->field("profit.*")
            ->select();
    }

    public static function getBalanceType()
    {
        return BalanceType::where(["status" => 1])->select();
    }

    public static function getBalanceTypeById($balanceTypeId)
    {
        return BalanceType::where(["status" => 1, "id" => $balanceTypeId])->find();
    }

    /**
     * 查询下级用户
     * @param $mainType
     * @param $mainId
     * @return bool|false|\PDOStatement|string|\think\Collection
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getChildUser($mainType, $mainId)
    {
        return Db::name("user")->alias("u")
            // ->join("bank_card b", "b.sale_id = u.id", "LEFT")
            ->where(["ptype" => $mainType, "pid" => $mainId, "is_virtual" => 1])
            ->field("u.id,u.nickname as name,u.mobile as phone")
            ->order("createtime desc")
            ->select();
    }

    public static function getMyEquip($mainType, $mainId, $productId)
    {
        return Equip::where(["main_type" => $mainType, "main_id" => $mainId, "product_id" => $productId, "binding_time" => [">", 0]])->order("binding_time desc")->select();
    }

    public static function getEquipList($mainType, $mainId, $productId, $pageSize, $sn = "", $status = 0)
    {
        $query = Equip::where(["main_type" => $mainType, "main_id" => $mainId])->where(["product_id" => $productId]);

        if (!empty($sn)) {
            $query->where("sn", "like", "%" . $sn . "%");
        }

        if ($status > 0) {
            if ($status == 1) {
                $query->where(["active_time" => [">", 0], "binding_time" => [">", 0]]);
            } elseif ($status == 2) {
                $query->where(["active_time" => 0]);
            } elseif ($status == 3) {
                $query->where(["binding_time" => 0]);
            } elseif ($status == 4) {
                $query->where(["binding_time" => [">", 0], "active_time" => 0]);
            } elseif ($status == 5) {
                $query->where(["binding_time" => [">", 0]]);
            }
        }

        return $query->order("active_time desc,binding_time desc,id")->paginate($pageSize);
    }

    public static function getBalanceSum($mainType, $mainId, $startTime, $endTime)
    {
        return Db::name("balance_log")->where(["main_id" => $mainId,'earn_type' => 1])->whereBetween("create_time", [$startTime, $endTime])->sum("money");
    }

    public static function getMoneySum($mainType, $mainId, $startTime, $endTime)
    {
        return MoneyLogs::where(["main_type" => $mainType, "main_id" => $mainId, "type" => 1])->whereBetween("trading_time", [$startTime, $endTime])->sum("changes");
    }

    public static function getMoneyLogs($mainType, $mainId, $pageSize)
    {
        return MoneyLogs::where(["main_type" => $mainType, "main_id" => $mainId])->order("create_time desc")->paginate($pageSize);
    }

    public static function getMoney($mainType, $mainId, $balanceId = 0, $type)
    {
        $query = Money::where(["main_type" => $mainType, "main_id" => $mainId, "type" => $type]);
        if ($balanceId != 0) {
            $result = $query->where(["balance_type_id" => $balanceId])->find();
        } else {
            $result = $query->select();
        }
        return $result;
    }

    /**
     * 根据费率查询费率ID
     * @param $tranName
     * @return array|bool|false|\PDOStatement|string|\think\Model|null
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getRateByName($tranName)
    {
        return Rate::where("start", "<=", $tranName)->where("end", ">=", $tranName)->select();
    }

    public static function getTransactionTypeByName($tranName)
    {
        return TransactionType::where(["name" => $tranName])->find();
    }

    public static function getTransactionTypeByCode($code)
    {
        return TransactionType::where(["code" => $code])->find();
    }

    public static function getChannelById($id)
    {
        return ConsumeChannel::get($id);
    }

    public static function getChannelByCode($code)
    {
        return ConsumeChannel::where(["code" => $code])->find();
    }

    /**
     * 查询政策信息
     * @param $policyId
     * @return Policy|null
     * @throws \think\exception\DbException
     */
    public static function getPolicy($policyId)
    {
        return Policy::get($policyId);
    }

    public static function getProfitRate($profitId, $cardType, $tranTypeId, $rateId)
    {
        return ProfitDetails::where(["profit_id" => $profitId, "card_type" => $cardType, "transaction_type_id" => $tranTypeId, "rate_id" => $rateId])->find();
    }

    public static function getPolicyDetails($policyId)
    {
        return Db::name("policy")->alias("policy")
            ->join("stardard s", "s.id = policy.stardard_id", "LEFT")
            ->join("flow f", "f.id = policy.flow_id", "LEFT")
            ->join("cash c", "c.id = policy.cash_id", "LEFT")
            ->join("profit p", "p.id = policy.profit_id", "LEFT")
            ->where(["policy.id" => $policyId])
            ->field("policy.title as policy_title,p.title as profit_title,s.title as standard_title,f.title as flow_title,c.title as cash_title")
            ->find();
    }

    /**
     * 根据设备号，查询上下级
     * @param $sn
     * @return array|bool|false|\PDOStatement|string|\think\Model|null
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getLevelInfo($sn)
    {
        return Db::name("allocation")->alias("a")
            ->join("allocation_details d", "a.id = d.allocation_id", "LEFT")
            ->where(["d.sn" => $sn])
            ->order("a.create_time desc")
            ->find();
    }

    /**
     * 查询验证码
     * @param $mobile
     * @return array|bool|false|\PDOStatement|string|\think\Model|null
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getSms($mobile)
    {
        return SmsLogs::where(["sms_phone" => $mobile])->order("sms_create_time desc")->find();
    }

    /**
     * 根据产品查询分润设置
     * @param $mainType
     * @param $mainId
     * @param $productId
     * @return array|bool|false|\PDOStatement|string|\think\Model|null
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getProfitByProduct($mainType, $mainId, $productId)
    {
        return Db::name("profit")->alias("profit")
            ->join("policy policy", "profit.id = policy.profit_id", "LEFT")
            ->join("company_stocks stocks", "policy.id = stocks.policy_id", "LEFT")
            ->where(["stocks.main_type" => $mainType, "stocks.main_id" => $mainId, "stocks.product_id" => $productId])
            ->field("profit.*")
            ->find();
    }

    /**
     * 查询机构拥有的产品ID
     * @param $mainType
     * @param $mainId
     * @return array|bool|string
     */
    public static function getCompanyProducts($mainType, $mainId)
    {
        return CompanyStocks::where(["main_type" => $mainType, "main_id" => $mainId])->column("product_id");
    }

    /**
     * 通过调拨号查询详情
     * @param $allocationId
     * @return bool|false|\PDOStatement|string|\think\Collection
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getAllocationDetails($allocationId)
    {
        return AllocationDetails::where(["allocation_id" => $allocationId])->select();
    }

    /**
     * 根据政策查询分润设置
     * @param $policyId
     * @return array|bool|false|\PDOStatement|string|\think\Model|null
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getProfitByPolicy($policyId)
    {
        return Db::name("profit")->alias("profit")
            ->join("policy policy", "policy.profit_id = profit.id", "LEFT")
            ->where(["policy.id" => $policyId])
            ->field("*,profit.id as profit_id,policy.id as policy_id")
            ->find();
    }

    /**
     * 根据设备号，调拨ID查询调拨详情
     * @param $sn
     * @param $allocationId
     * @return array|bool|false|\PDOStatement|string|\think\Model|null
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getAllocationBySn($sn, $allocationId)
    {
        return AllocationDetails::where(["allocation_id" => $allocationId, "sn" => $sn])->find();
    }

    public static function getAllocationById($id)
    {
        return Allocation::get($id);
    }

    /**
     * 设备号查询设备信息
     * @param $sn
     * @return array|bool|false|\PDOStatement|string|\think\Model|null|Equip
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getEquip($sn)
    {
        return Equip::where(["sn" => $sn])->where("status", "not in", [5])->find();
    }

    /**
     * 库存ID查询库存详情
     * @param $confirmId
     * @return bool|false|\PDOStatement|string|\think\Collection
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getStocksDetails($confirmId)
    {
        return CompanyStocksDetails::where(["confirm_id" => $confirmId])->select();
    }

    /**
     * 查询顶级机构库存
     * @return bool|false|\PDOStatement|string|\think\Collection
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getAdminCompanyStocks()
    {
        return Db::name("company_stocks")->alias("stock")
            ->join("company", "stock.main_id = company.id", "LEFT")
            ->where(["stock.main_type" => 1, "company.pid" => 0])
            ->field("company.name,stock.stocks")
            ->select();
    }

    /**
     * 根据机构查询库存
     * @param $companyId
     * @return array|bool|false|\PDOStatement|string|\think\Model|null
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getStocksByCompany($companyId)
    {
        return CompanyStocks::where(["main_type" => 1, "main_id" => $companyId])->find();
    }

    /**
     * 父级机构查询子级机构
     * @param $parentIds
     * @return array|bool|string
     */
    public static function getChildCompanyIds($parentIds)
    {
        return Company::where(["pid" => $parentIds])->column("id");
    }

    /**
     * 根据产品查询机构库存
     * @param $mainType
     * @param $mainId
     * @param $productId
     * @return array|bool|false|\PDOStatement|string|\think\Model|null
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getStocks($mainType, $mainId, $productId)
    {
        return CompanyStocks::where(["main_type" => $mainType, "main_id" => $mainId, "product_id" => $productId])->find();
    }

    /**
     * 多条件查询分润
     * @param $policyId
     * @param $cardType
     * @param $tran
     * @param $rate
     * @param $channel
     * @return array|bool|false|\PDOStatement|string|\think\Model|null
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getProfitDetailsByParams($policyId, $cardType, $tran, $rate, $channel)
    {
        return PolicyProfit::where(["policy_id" => $policyId, "card_type" => $cardType, "transaction_type_id" => $tran, "rate_id" => $rate, "channel_id" => $channel])->find();
    }

    public static function getProfitDetailsByParam($policyId, $cardType, $tran, $channel, $policyRate, $rate)
    {
        $policyRate = explode(",", $policyRate);
        if (count($policyRate) == 1) {
            return PolicyProfit::where(["policy_id" => $policyId, "card_type" => $cardType, "transaction_type_id" => $tran, "rate_id" => $policyRate[0], "channel_id" => $channel])->find();
        } else {
            $rateObj = Lib::getRateByName($rate);
            if (!$rateObj) {
                return false;
            } else if (count($rateObj) == 1) {
                return PolicyProfit::where(["policy_id" => $policyId, "card_type" => $cardType, "transaction_type_id" => $tran, "rate_id" => $rateObj[0], "channel_id" => $channel])->find();
            } else {
                $profit = PolicyProfit::where(["policy_id" => $policyId, "card_type" => $cardType, "transaction_type_id" => $tran, "channel_id" => $channel])->select();
                if (count($profit) == 0) {
                    return false;
                } else if (count($profit) == 1) {
                    return $profit[0];
                } else if (count($profit) == 2) {
                    if ($profit[0]->profit_hide == 0 && $profit[1]->profit_hide == 0 && $profit[0]->profit_base == 0 && $profit[1]->profit_base == 0) {
                        return $profit[0];
                    } else {
                        if ($profit[0]->profit_hide != 0 || $profit[1]->profit_hide != 0) {
                            if ($profit[0]->profit_hide == 0) {
                                return $profit[1];
                            } else if ($profit[1]->profit_hide == 0) {
                                return $profit[0];
                            } else {
                                return $profit[0]->profit_hide > $profit[1]->profit_hide ? $profit[1] : $profit[0];
                            }
                        } else {
                            if ($profit[0]->profit_base == 0 && $profit[1]->profit_base == 0) {
                                return false;
                            } else if ($profit[0]->profit_base == 0) {
                                return $profit[1];
                            } else if ($profit[1]->profit_base == 0) {
                                return $profit[0];
                            } else {
                                return $profit[0]->profit_base > $profit[1]->profit_base ? $profit[1] : $profit[0];
                            }
                        }
                    }
                }
            }
        }
    }

    /**
     * 根据分润ID查询分润详情
     * @param $profitId
     * @return bool|false|\PDOStatement|string|\think\Collection
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getProfitDetails($profitId)
    {
        return ProfitDetails::where(["profit_id" => $profitId])->select();
    }

    public static function getRateList(array $ids)
    {
        return Rate::where("id", "in", $ids)->select();
    }

    public static function getRate($id)
    {
        return Rate::get($id);
    }

    public static function getTransactionList(array $ids)
    {
        return TransactionType::where("id", "in", $ids)->select();
    }

    public static function getTransaction($id)
    {
        return TransactionType::get($id);
    }

    public static function getTransactionByOrderNum($orderNum)
    {
        return Xyf::where(["order_num" => $orderNum])->find();
    }

    public static function getChannelByIds($ids)
    {
        $ids = explode(",", $ids);
        return ConsumeChannel::where("id", "in", $ids)->select();
    }

    /**
     * 根据IDs查询交易类型
     * @param string $ids
     * @return bool|false|\PDOStatement|string|\think\Collection
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getTransactionTypeBYIds(string $ids)
    {
        $ids = explode(",", $ids);
        return TransactionType::where("id", "in", $ids)->select();
    }

    /**
     * 根据IDs查询费率
     * @param $ids
     * @return bool|false|\PDOStatement|string|\think\Collection
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getRateListByIds(string $ids)
    {
        $ids = explode(",", $ids);
        return Rate::where("id", "in", $ids)->select();
    }

    /**
     * 根据ID查询产品
     * @param $id
     * @return Product|null
     * @throws \think\exception\DbException
     */
    public static function getProductById($id)
    {
        return Product::get($id);
    }

    /**
     * 根据代理ID查询代理信息
     * @param $id
     * @return User|null
     * @throws \think\exception\DbException
     */
    public static function getUserInfo($id)
    {
        return User::get($id);
    }

    /**
     * 根据pid查询机构级别
     * @param $pid
     * @return array|bool|false|\PDOStatement|string|\think\Model|null
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getCompanyLevelByPid($pid)
    {
        return CompanyLevel::where(["pid" => $pid])->find();
    }

    /**
     * 根据机构ID查询
     * @param $id
     * @return Company|null
     * @throws \think\exception\DbException
     */
    public static function getCompany($id)
    {
        return Company::get($id);
    }

    /**
     * 根据机构后台账号ID查询
     * @param $adminId
     * @return array|bool|false|\PDOStatement|string|\think\Model|null
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getCompanyByAdminId($adminId)
    {
        return Company::where(["admin_id" => $adminId])->find();
    }

    /**
     * 根据代理ID和产品ID获取代理自己的政策
     */
    public static function getProductPolicy($mainType, $mainId, $productId)
    {
        return Db::name("policy_relation")->alias("a")
            ->join("policy b", "b.id = a.policy_id", "LEFT")
            ->where(["a.main_type" => $mainType, "a.main_id" => $mainId, "b.product_id" => $productId])
            ->group("a.policy_id")
            ->field("b.*")
            ->select();
    }

    /**
     * 根据产品ID获取自己设置的政策
     */
    public static function getSubordinatePolicy($productId, $mainType, $mainId)
    {
        $where = [
            "product_id" => $productId,
            "status" => 1,
            "main_type" => $mainType,
            "main_id" => $mainId
        ];
        return Policy::where($where)->order('id desc')->select();
    }

    public static function getPolicyCash($policyId)
    {
        return PolicyCash::where(['policy_id' => $policyId])->find();
    }

    public static function getPolicyFlow($policyId)
    {
        return PolicySimcard::where(['policy_id' => $policyId])->find();
    }

    public static function getPolicyStandard($policyId)
    {
        return PolicyStandard::where(['policy_id' => $policyId])->select();
    }

    public static function getPolicyProfit($policyId, $cardId, $tranId, $rateId, $channelId)
    {
        return PolicyProfit::where(["policy_id" => $policyId, "card_type" => $cardId, "transaction_type_id" => $tranId, "rate_id" => $rateId, "channel_id" => $channelId])->find();
    }

    public static function getMyPolicyByProduct($mainType, $mainId, $productId)
    {
        return Policy::where(["main_type" => $mainType, "main_id" => $mainId, "product_id" => $productId])->select();
    }

    /**
     * 查询用户产品
     * @param $mainType
     * @param $mainId
     * @param $type || 查询类型，default-已设置默认政策的产品，all-所有产品
     * @return bool|false|\PDOStatement|string|\think\Collection
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function getUserProduct($mainType, $mainId, $type = "default")
    {
        $query = Db::name("policy_relation")->alias("r")
            ->join("product p", "r.product_id = p.id", "LEFT")
            ->where(["r.main_type" => $mainType, "r.main_id" => $mainId])
            ->where(["r.product_id" => [">", 0]]);
        if ($type == "default") {
            $query->where(["r.default" => 1]);
        }
        return $query->group("r.product_id")
            ->field("p.id,p.name,r.policy_id")
            ->select();
    }

    /**
     * 获取政策详情
     */
    public static function getPolicyDetail($policyId)
    {
        $policy = Policy::get($policyId);
        $policy_profit = PolicyProfit::where('policy_id', $policyId)->select();
        $policy->policy_profit = [];
        $policy->policy_profit_num = 0;
        if (!empty($policy_profit)) {
            $nums = 0;
            foreach ($policy_profit as $key => $val) {
                $policy_profit[$key]["rate"] = Rate::where('id', $val["rate_id"])->value('name');
                $policy_profit[$key]["tran"] = TransactionType::where('id', $val["transaction_type_id"])->value('name');
                $policy_profit[$key]["card"] = $val["card_type"] == 1 ? '贷记卡' : '借记卡';
                $policy_profit[$key]["channel"] = ConsumeChannel::where('id', $val["channel_id"])->value('name');
                if ($val->profit_base > 0 || $val->profit_hide > 0) {
                    $nums++;
                }
            }
            $policy->policy_profit = $policy_profit;
            if ($nums > 0) {
                $policy->policy_profit_num = count($policy_profit);
            }
        }
        $policy_cash = PolicyCash::where('policy_id', $policyId)->find();
        $policy->policy_cash = [];
        $policy->policy_cash_num = 0;
        if (!empty($policy_cash)) {
            $policy->policy_cash = $policy_cash;
            if ($policy_cash->base > 0 || $policy_cash->hide > 0) {
                $policy->policy_cash_num = 1;
            }
        }
        $policy_simcard = PolicySimcard::where('policy_id', $policyId)->find();
        $policy->policy_simcard = [];
        $policy->policy_simcard_num = 0;
        if (!empty($policy_simcard)) {
            $policy->policy_simcard = $policy_simcard;
            if ($policy_simcard->base > 0 || $policy_simcard->hide > 0) {
                $policy->policy_simcard_num = 1;
            }
        }
        $policy_standard = PolicyStandard::where('policy_id', $policyId)->select();
        $policy->policy_standard = [];
        $policy->policy_standard_num = 0;
        if (!empty($policy_standard)) {
            $policy->policy_standard = $policy_standard;
            $nums = 0;
            foreach ($policy_standard as $val) {
                if ($val->base > 0 || $val->hide > 0) {
                    $nums++;
                }
            }
            if ($nums > 0) {
                $policy->policy_standard_num = count($policy_standard);
            }
        }
        return $policy;
    }

    /**
     * 获取我的产品
     */
    public static function getMyProduct($mainId, $mainType)
    {
        return Db::name("policy_relation")->alias("a")
            ->join("product b", "b.id = a.product_id", "LEFT")
            ->where(["a.main_type" => $mainType, "a.main_id" => $mainId])
            ->where('policy_id', '>', 0)
            ->group("a.product_id")
            ->order("b.create_time desc")
            ->field("b.id,b.name,b.table_name")
            ->select();
    }

    /**
     * 获取我的产品
     */
    public static function getMyProduct5($mainId, $mainType)
    {
        // return Db::name("allocation")->alias("a")
        //     ->join("product b", "b.id = a.product_id", "LEFT")
        //     ->where(["a.to_type" => $mainType, "a.to" => $mainId])
        //     ->group("a.product_id")
        //     ->order("b.create_time desc")
        //     ->field("b.id,b.name,b.table_name")
        //     ->select();

        return Db::name("product")->field("id,name,table_name")->where('status', 1)->order("create_time desc")->select();
    }

    /**
     * 获取我的产品
     */
    public static function getMyProductData($mainId, $mainType)
    {
        return Db::name("policy_relation")->alias("a")
            ->join("product b", "b.id = a.product_id", "LEFT")
            ->where(["a.main_type" => $mainType, "a.main_id" => $mainId])
            ->where('policy_id', '>', 0)
            ->group("a.product_id")
            ->order("b.create_time desc")
            ->field("b.id,b.name label")
            ->select();
    }

    /**
     * 获取我的产品
     */
    public static function getMyProductData5($mainId, $mainType)
    {
        return Db::name("allocation")->alias("a")
            ->join("product b", "b.id = a.product_id", "LEFT")
            ->where(["a.to_type" => $mainType, "a.to" => $mainId])
            ->group("a.product_id")
            ->order("b.create_time desc")
            ->field("b.id,b.name label")
            ->select();
    }

    /**
     * 获取用户政策
     */
    public static function getUserPolicy($mainType, $mainId, $productId)
    {
        return Db::name("policy_relation")->alias("a")
            ->join("policy b", "b.id = a.policy_id", "LEFT")
            ->where(["a.main_type" => $mainType, "a.main_id" => $mainId, "a.product_id" => $productId])
            ->where(['b.status' => 1])
            ->where('policy_id', '>', 0)
            ->group("a.policy_id")
            ->field("a.id,b.title,a.default,a.product_id")
            ->select();
    }

    /**
     * 获取团队申请产品记录
     */
    public static function getTeamProductApplyList($mainType, $mainId)
    {
        $data = Db::name("product_apply")->alias("a")
            ->join("user b", 'b.id=a.main_id', 'LEFT')
            ->where(['b.pid' => $mainId, 'a.main_type' => $mainType, 'a.status' => 0])
            ->field('a.id,b.nickname name,a.product_ids,from_unixtime(a.create_time)')
            ->order('a.create_time')
            ->select();
        foreach ($data as $key => $val) {
            $data[$key]["product"] = self::getProductName($val['product_ids']);
        }
        return $data;
    }

    /**
     * 获取产品名称
     */
    public static function getProductName($ids)
    {
        $data = Product::whereIn('id', $ids)->select();
        $str = [];
        $names = '';
        foreach ($data as $val) {
            $str[] = $val->name;
        }
        if (!empty($str)) {
            $names = implode(',', $str);
        }
        return $names;
    }

    public static function getDefaultPolicy($mainType, $mainId, $productId)
    {
        return PolicyRelation::where(["main_type" => $mainType, "main_id" => $mainId, "product_id" => $productId, "default" => 1])->find();
    }

    /**
     * 获取团队所有代理
     */
    public static function getTeamAllUser($mainId, $type = 1, $startTime = null, $endTime = null)
    {
        $sql = "select id from fd_user where status = 'normal' and is_virtual = 1 and FIND_IN_SET(id, queryChildrenUser(" . $mainId . "))";
        if ($type == 2) {
            $sql .= " and id <> " . $mainId;
        }
        if (!empty($startTime) && !empty($endTime)) {
            $sql .= " and (createtime >= " . $startTime . " and createtime <= " . $endTime . ")";
        }
        $ids = [];
        $data = Db::query($sql);
        foreach ($data as $val) {
            $ids[] = $val["id"];
        }
        return $ids;
    }

    /**
     * 获取团队所有代理
     */
    public static function getTeamAllUser5($mainId, $type = 1, $startTime = null, $endTime = null)
    {
        $sql = "select queryChildrenUser(" . $mainId . ") as ids";
        $data = Db::name("")->query($sql);
        $teamIds = explode(",", $data[0]["ids"]);
        $ids = [];
        if ($type == 2) {
            for ($i = 2; $i < count($teamIds); $i++) {
                $ids[] = $teamIds[$i];
            }
        } else {
            for ($i = 1; $i < count($teamIds); $i++) {
                $ids[] = $teamIds[$i];
            }
        }
        return $ids;
    }

    /**
     * 获取团队交易数据 - 统计
     */
    public static function getTeamTranData($teamIds, $tableName, $productId, $type,$userIds,$tranType, $startTime = null, $endTime = null, $search = null)
    {
        $tran = Db::name($tableName)->alias("a")
            ->join("equip b", "a.sn = b.sn and b.status < 3", "LEFT")
            ->whereIn('b.main_id', $userIds)
            ->where('b.main_type', 2)
            ->where('b.product_id', $productId)
            ->where('a.trading_time', '<', strtotime(date('Y-m-d')));
        if (!empty($search)) {
            $tran->where('a.sn|b.user_info', 'like', '%' . $search . '%');
        }
        $tran->where('a.trading_time', 'between', [$startTime, $endTime]);
        if($tranType > 0){
            $tran->where('a.transaction_type', '=',$tranType);
        }
        return $tran->field("count(a.id) total, IFNULL(sum(a.transaction_amount), '0.00') sum")->find();
    }

    /**
     * 获取团队交易数据 - 统计
     */
    public static function getTeamTranDataNew($teamIds, $productId, $type, $startTime = null, $endTime = null, $search = null)
    {
        $tran = Db::name("transaction_statistics")
            ->whereIn('main_id', $teamIds)
            ->whereIn('product_id', $productId)
            ->where('day', '<', date('Y-m-d'));
        if ($type == 2) {
            $tran->where('day', 'between', [$startTime, $endTime]);
        }
        $data = $tran->field("IFNULL(sum(total_amount), '0.00') sum")->find();
        return $data;
    }

    /**
     * 获取团队激活数据 - 统计
     */
    public static function getTeamActiveData($teamIds, $productId, $type, $startTime = null, $endTime = null, $search = null)
    {
        $tran = Db::name("equip")
            ->whereIn('main_id', $teamIds)
            ->where('main_type', '=', 2)
            ->where('product_id', '=', $productId)
            ->where('active_time', '>', 0)
            ->where('active_time', '<', strtotime(date('Y-m-d')));
        if ($type == 2) {
            $tran->where('active_time', 'between', [$startTime, $endTime]);
        }
        if (!empty($search)) {
            $tran->where('sn', 'like', '%' . $search . '%');
        }
        $data = $tran->field('count(id) total')->find();
        if (empty($data)) {
            $data = ['total' => 0];
        }
        return $data;
    }

    /**
     * 获取团队激活数据 - 统计 5.0
     */
    public static function getTeamActiveData5($teamIds, $productId, $type, $startTime = null, $endTime = null, $search = null)
    {
        $tran = Db::name("equip")
            ->whereIn('main_id', $teamIds)
            ->where('main_type', '=', 2)
            ->whereIn('product_id', $productId)
            ->where('active_time', '>', 0)
            ->where('active_time', '<', strtotime(date('Y-m-d')));
        if ($type == 2) {
            $tran->where('active_time', 'between', [$startTime, $endTime]);
        }
        if (!empty($search)) {
            $tran->where('sn', 'like', '%' . $search . '%');
        }
        $data = $tran->field('count(id) total')->find();
        if (empty($data)) {
            $data = ['total' => 0];
        }
        return $data;
    }

    /**
     * 按产品和时间分组统计 - 交易
     */
    public static function getProductDateGroup($teamIds, $productId, $tableName, $startTime, $type = 1, $date = null)
    {
        $query = Db::name($tableName)->alias("a")
            ->join("equip b", "a.sn = b.sn and b.status < 3", "LEFT")
            ->whereIn('b.main_id', $teamIds)
            ->where('b.main_type', 2)
            ->where('b.product_id', $productId)
            ->where('a.trading_time', '>', $startTime)
            ->where('a.trading_time', '<', strtotime(date('Y-m-d')));
        if ($type == 2) {
            $query->where("FROM_UNIXTIME(a.trading_time, '%Y-%m') = '" . $date . "'");
            $query->group("FROM_UNIXTIME(a.trading_time, '%Y-%m-%d')");
            $fields = "sum(a.transaction_amount) total,FROM_UNIXTIME(a.trading_time, '%Y-%m-%d') date";
        } else {
            $query->group("FROM_UNIXTIME(a.trading_time, '%Y-%m')");
            $fields = "sum(a.transaction_amount) total,FROM_UNIXTIME(a.trading_time, '%Y-%m') date";
        }
        $query->field($fields);
        $data = $query->order('date desc')->select();
        return $data;
    }

    /**
     * 数据 获取交易详情
     */
    public static function getTranDetailList($teamIds, $productId, $tableName, $startTime, $endTime, $search, $pageSize)
    {
        $query = Db::name($tableName)->alias("a")
            ->join("equip b", "a.sn = b.sn and b.status < 3", "LEFT")
            ->whereIn('b.main_id', $teamIds)
            ->where('b.main_type', 2)
            ->where('b.product_id', $productId)
            ->where('a.trading_time', '<', strtotime(date('Y-m-d')));

        if ($startTime > 0 && $endTime > 0 && $startTime < $endTime) {
            $query->where('a.trading_time', 'between', [$startTime, $endTime]);
        }
        if (!empty($search)) {
            $query->where('a.merchant_name|a.merchant_id|a.sn', 'like', '%' . $search . '%');
        }
        $fields = "a.sn,a.merchant_name name,a.transaction_amount money,'交易成功' as remarks";
        return $query->field($fields)->paginate($pageSize);
    }

    /**
     * 数据 获取交易详情
     */
    public static function getTranDetailList5($teamIds, $productId, $tableName,$userIds, $startTime, $endTime, $search,$tranType, $pageSize)
    {
        $query = Db::name($tableName)->alias("a")
            ->join("equip b", "a.sn = b.sn and b.status < 3", "LEFT")
            ->where('b.main_type', 2)
            ->where('b.product_id', $productId)
            ->where('b.main_id',"in", $userIds)
            ->where('a.trading_time', '<', strtotime(date('Y-m-d')));

        if ($startTime > 0 && $endTime > 0 && $startTime < $endTime) {
            $query->where('a.trading_time', 'between', [$startTime, $endTime]);
        }
        if (!empty($search)) {
            $query->where('a.sn|b.user_info', 'like', '%' . $search . '%');
        }
        if($startTime > 0 && $endTime > 0){
            $query->where('a.trading_time', 'between', [$startTime, $endTime]);
        }
        if($tranType > 0){
            $query->where('a.tran_type', '=',$tranType);
        }
        $fields = "a.transaction_type,a.tran_type,a.sn,b.user_info name,a.transaction_amount money,'交易成功' as remarks,a.trading_time,FROM_UNIXTIME(a.trading_time, '%Y-%m-%d %H:%i:%s') as trading_time_text";
        return $query->field($fields)->order("trading_time desc")->select();
    }

    /**
     * 按产品和时间分组统计 - 激活
     */
    public static function getProductDateActiveGroup($teamIds, $productId, $startTime, $type = 1, $date = null)
    {
        $query = Db::name('equip')
            ->whereIn('main_id', $teamIds)
            ->where('main_type', 2)
            ->where('product_id', $productId)
            ->where('status', '<', 3)
            ->where('active_time', '>', 0)
            ->where('active_time', '<', strtotime(date('Y-m-d')));
        if (!empty($startTime) && $startTime > 0) {
            $query->where("active_time", '>', $startTime);
        }
        if ($type == 2) {
            $query->where("FROM_UNIXTIME(active_time, '%Y-%m') = '" . $date . "'");
            $query->group("FROM_UNIXTIME(active_time, '%Y-%m-%d')");
            $fields = "count(id) total,FROM_UNIXTIME(active_time, '%Y-%m-%d') date";
        } else {
            $query->group("FROM_UNIXTIME(active_time, '%Y-%m')");
            $fields = "count(id) total,FROM_UNIXTIME(active_time, '%Y-%m') date";
        }
        $query->field($fields);
        $data = $query->order('date desc')->select();
        return $data;
    }

    /**
     * 数据 获取交易详情
     */
    public static function getActiveDetailList($teamIds, $productId, $tableName, $startTime, $endTime, $search, $pageSize)
    {
        $query = Db::name('equip')->alias("a")
            ->join($tableName . " b", "b.sn = a.sn", "LEFT")
            ->whereIn('a.main_id', $teamIds)
            ->where('a.main_type', 2)
            ->where('a.product_id', $productId)
            ->where('a.status', '<', 3)
            ->where('a.active_time', '>', 0)
            ->where('a.active_time', '<', strtotime(date('Y-m-d')));

        if ($startTime > 0 && $endTime > 0 && $startTime < $endTime) {
            $query->where('a.active_time', 'between', [$startTime, $endTime]);
        }
        if (!empty($search)) {
            $query->where('a.sn|b.merchant_name|b.merchant_id', 'like', '%' . $search . '%');
        }
        $fields = "a.sn,a.user_info name,1 nums,'激活成功' as remarks,FROM_UNIXTIME(a.active_time, '%Y-%m-%d %H:%m:%s') active_time";
        return $query->field($fields)->group('b.sn')->paginate($pageSize);
    }

    /**
     * 数据 获取交易详情 5.0
     */
    public static function getActiveDetailList5($teamIds, $productId, $tableName, $startTime, $endTime, $search, $pageSize)
    {
        $query = Db::name('equip')->alias("a")
            ->join($tableName . " b", "b.sn = a.sn", "LEFT")
            ->whereIn('a.main_id', $teamIds)
            ->where('a.main_type', 2)
            ->where('a.product_id', $productId)
            ->where('a.status', '<', 3)
            ->where('a.active_time', '>', 0)
            ->where('a.active_time', '<', strtotime(date('Y-m-d')));

        if ($startTime > 0 && $endTime > 0 && $startTime < $endTime) {
            $query->where('a.active_time', 'between', [$startTime, $endTime]);
        }
        if (!empty($search)) {
            $query->where('a.sn|b.merchant_name|b.merchant_id', 'like', '%' . $search . '%');
        }
        $fields = "a.sn,a.user_info name,1 nums,'激活成功' as remarks,FROM_UNIXTIME(a.active_time, '%Y-%m-%d %H:%m:%s') active_time";
        return $query->field($fields)->group('b.sn')->order("a.active_time desc")->select();
    }

    /**
     * 根据产品获取代理人数
     */
    public static function getProductTeam($mainId, $productId, $startTime = null, $endTime = null)
    {
        $query = Db::name('policy_relation')->alias("a")
            ->join("user b", "b.id = a.main_id", "LEFT")
            ->whereIn('a.main_id', $mainId)
            ->where('a.main_type', 2)
            ->where('a.product_id', $productId)
            ->group('a.product_id');
        if ($startTime > 0 && $endTime > 0 && $startTime < $endTime) {
            $query->where('b.createtime', 'between', [$startTime, $endTime]);
        }
        $data = $query->field('count(a.id) total')->find();
        return !empty($data["total"]) ? $data["total"] : 0;
    }

    /**
     * 获取我的直属团队ID
     */
    public static function getMyTeamId($mainId)
    {
        $data = User::where(["pid" => $mainId, "ptype" => 2, 'status' => 'normal'])->field('id')->order('createtime desc')->select();
        $ids = [];
        foreach ($data as $val) {
            $ids[] = $val['id'];
        }
        return $ids;
    }

    /**
     * 根据产品获取代理人数 - 详情
     */
    public static function getProductTeamDetail($mainId, $productId, $pageSize)
    {
        $fields = "b.id,b.username,b.nickname,b.mobile,b.avatar,FROM_UNIXTIME(b.createtime, '%Y-%m-%d %H:%m:%s') createtime";
        return Db::name('policy_relation')->alias("a")
            ->join("user b", "b.id = a.main_id", "LEFT")
            ->whereIn('a.main_id', $mainId)
            ->where('a.main_type', 2)
            ->where('a.product_id', $productId)
            ->group('a.main_id')
            ->field($fields)->order("b.createtime desc")->paginate($pageSize);
    }

    /**
     * 根据产品统计当日收益
     */
    public static function getProductDayIncome($mainType, $mainId, $productId, $date)
    {
        return Db::name('money_log')->where(["main_type" => $mainType, "main_id" => $mainId, "product_id" => $productId, "type" => 1, "is_withdrawal" => 0])
            ->where("FROM_UNIXTIME(trading_time, '%Y-%m-%d') = '" . $date . "'")
            ->field("IFNULL(sum(changes), '0.00') total,count(id) counts")
            ->find();
    }

    /**
     * 根据产品、收益类型统计当日收益
     */
    public static function getProductTypeDayIncome($mainType, $mainId, $productId, $date, $type = 1, $balanceTypeId = null)
    {
        $query = Db::name('money_log')->alias("a")
            ->join('balance_type b', 'b.id = a.balance_type_id', 'LEFT')
            ->where(["a.main_type" => $mainType, "a.main_id" => $mainId, "a.product_id" => $productId, "a.type" => 1, "a.is_withdrawal" => 0])
            ->where("FROM_UNIXTIME(a.trading_time, '%Y-%m-%d') = '" . $date . "'");
        if ($type == 1) {
            $query->group('a.balance_type_id');
            $fields = "b.id,b.name,IFNULL(sum(a.changes), '0.00') total,count(a.id) counts";
        } else {
            $query->where('a.balance_type_id', $balanceTypeId);
            $query->group('a.money_type');
            $fields = "(CASE a.money_type WHEN 'base' THEN '奖励' WHEN 'hide' THEN '备用' END) as money_type,IFNULL(sum(a.changes), '0.00') total,count(a.id) counts";
        }
        return $query->field($fields)->order('b.sort asc,b.create_time desc')
            ->select();
    }

    /**
     * 根据产品统计调拨
     */
    public static function getProductAllocationTotal($mainType, $mainId, $productId = null)
    {
        $query_d = Db::name('allocation')
            ->where(["type" => 1, "from_type" => $mainType, "from" => $mainId]);
        if (!empty($productId)) {
            $query_d->where(["product_id" => $productId]);
        }
        $data = $query_d->where("confirm_type", "=", 1)
            ->field("sum(number) as total")
            ->find();
        $query_t = Db::name('allocation')
            ->where(["type" => 2, "to_type" => $mainType, "to" => $mainId]);
        if (!empty($productId)) {
            $query_t->where(["product_id" => $productId]);
        }
        $return_data = $query_t->where("confirm_type", "=", 1)
            ->field("sum(number) as total")
            ->find();
        $all_total = empty($data) ? 0 : (int)$data["total"];
        $return_total = empty($return_data) ? 0 : (int)$return_data["total"];
        return $all_total - $return_total;
    }

    /**
     * 根据产品统计【激活、绑定、未绑定】
     */
    public static function getProductEquipStateTotal($mainType, $mainId, $productId)
    {
        $fields = "COUNT(status = 0 OR NULL) as leisure,COUNT(active_time > 0 OR NULL) as active,COUNT(binding_time > 0 OR NULL) as bind";
        $data = Db::name("equip")
            ->where(["main_type" => $mainType, "main_id" => $mainId, "product_id" => $productId])
            ->where("status", "<", 2)
            ->field($fields)
            ->find();
        // $allocation = self::getProductAllocationStateTotal($mainType, $mainId, $productId);
        // if (!empty($data)) {
        //     $data["leisure"] -= $allocation;
        // }
        return empty($data) ? ["leisure" => 0, "active" => 0, "bind" => 0] : $data;
    }

    /**
     * 根据产品统计【总库存】
     */
    public static function getProductEquipStocksTotal($mainType, $mainId, $productId = null)
    {
        $query = Db::name('allocation')
            ->where(["type" => 1, "to_type" => $mainType, "to" => $mainId]);
        if (!empty($productId)) {
            $query->where(["product_id" => $productId]);
        }
        $data = $query->where("confirm_type", "=", 1)
            ->field("sum(number) as total")
            ->find();

        //查询所有回退设备
        $rollback = self::getUserRollbackEquip($mainType, $mainId, $productId);

        return empty($data) ? 0 : (int)$data["total"] - $rollback;
    }

    /**
     * 根据产品统计【调拨中】
     */
    public static function getProductAllocationStateTotal($mainType, $mainId, $productId)
    {
        $query_t = Db::name('allocation')
            ->where(["type" => 1, "from_type" => $mainType, "from" => $mainId]);
        if (!empty($productId)) {
            $query_t->where(["product_id" => $productId]);
        }
        $data = $query_t->where("confirm_type", "=", 0)
            ->field("sum(number) as total")
            ->find();
        return empty($data) ? 0 : (int)$data["total"];
    }

    /**
     * 查询用户回退设备总数
     * @param $mainType
     * @param $mainId
     * @param $productId
     * @return bool|float|int|string|null
     */
    public static function getUserRollbackEquip($mainType, $mainId, $productId)
    {
        $query = Db::name('allocation')
            ->where(["type" => 2, "from_type" => $mainType, "from" => $mainId, "confirm_type" => 1]);

        if ($productId) {
            $query->where(["product_id" => $productId]);
        }

        return $query->sum("number");
    }

    /**
     * 根据产品获取代理
     */
    public static function getProductTeams($mainId, $productId, $pageSize = 999)
    {
        $fields = "b.id,b.nickname name,b.mobile,'TEAM' type,'' selected";
        return Db::name('policy_relation')->alias("a")
            ->join("user b", "b.id = a.main_id", "LEFT")
            ->where(['b.pid' => $mainId, 'b.ptype' => 2])
            ->where('a.main_type', 2)
            ->where('a.product_id', $productId)
            ->group('a.main_id')
            ->field($fields)->order("b.createtime asc")->paginate($pageSize);
    }

    /**
     * 获取直属代理
     */
    public static function getProductTeams5($mainId, $productId, $pageSize = 999)
    {
        $fields = "id,nickname name,mobile,'TEAM' type,'' selected";
        return Db::name('user')
            ->where(['pid' => $mainId, 'ptype' => 2, 'status' => 'normal', 'is_virtual' => 1])
            ->field($fields)->order("createtime asc")->paginate($pageSize);
    }

    public static function getUserEquipActive($mainType, $mainId, $productId, $self = false)
    {
        if ($self) {
            $team = self::getTeamAllUser($mainId, 1);
            $allocationSn = Db::name("allocation")
                ->where(["type" => 1, "confirm_type" => 1, "to_type" => $mainType, "to" => $mainId])
                ->where(["product_id" => $productId])
                ->sum("number");
            $rollback = Db::name("allocation")
                ->where(["type" => 2, "confirm_type" => ["<>", 2], "from_type" => $mainType, "from" => $mainId])
                ->where(["product_id" => $productId])
                ->sum("number");
        } else {
            $team = self::getTeamAllUser($mainId, 2);
            $allocationSn = Db::name("allocation")
                ->where(["type" => 1, "confirm_type" => 1, "from_type" => $mainType, "from" => $mainId])
                ->where(["product_id" => $productId])
                ->sum("number");
            $rollback = Db::name("allocation")
                ->where(["type" => 2, "confirm_type" => ["<>", 2], "to_type" => $mainType, "to" => $mainId])
                ->where(["product_id" => $productId])
                ->sum("number");
        }
        $activeEquip = Db::name("equip")->where(['main_type' => 2, "product_id" => $productId])->where("main_id", "in", $team)->where(['active_time' => [">", 0]])->count();
        $total = $allocationSn - $rollback;
        $active = $activeEquip;
        $noactiveNum = $total - $active;
        return ["total" => $total, "activeNum" => $active, "noactiveNum" => ($noactiveNum > 0) ? $noactiveNum : 0];
    }

    public static function getUserEquipActive5($mainType, $mainId, $productId, $self = false)
    {
        $team = self::getTeamAllUser($mainId, 2);
        $fields = "COUNT(status = 0 OR NULL) as leisure,COUNT(active_time > 0 OR NULL) as active,COUNT(binding_time > 0 OR NULL) as bind,COUNT(*) as total";
        $inventory = Db::name("equip")
            ->field($fields)
            ->where(['main_type' => 2, "product_id" => $productId])
            ->whereIn("main_id", $team)
            ->where("status", '<', 2)
            ->find();

        // $rollback = Db::name("allocation")
        //     ->where(["type" => 2, "confirm_type" => ["=", 1], "to_type" => $mainType, "to" => $mainId])
        //     ->where(["product_id" => $productId])
        //     ->sum("number");

        $total = $inventory["total"];
        $active = $inventory["active"];
        $noactiveNum = $total - $active;

        return ["total" => $total, "activeNum" => $active, "noactiveNum" => $noactiveNum, "leisure" => $inventory["leisure"]];
    }
}
